import pymysql
import traceback


# 我想去用数据库工具去连接数据库，需要哪些要素？
# ip地址 ：host
# 端口号：port
# 账号：user
# 密码：password
# 数据库的名称：database
# cursorclass=pymysql.cursors.DictCursor ，返回的结果，以字典的方式返回
# 通过数据库连接的参数，赋值给一个数据库对象
"""db_obj = pymysql.connect(host = "47.104.65.165",port=3306,user="root",password="123456",database="jiac",cursorclass=pymysql.cursors.DictCursor)"""

# 通过数据库对象创建：游标,游标就相当于回车这个动作，执行sql用的
"""cursor = db_obj.cursor()"""

# 执行sql：查询
"""cursor.execute(f"select * from student where name = 'baijiacheng'")
result = cursor.fetchone() # 执行sql，返回符合条件的一条数据
print(result)"""

# 执行sql：新增 ，新增是对数据库有改动的
"""cursor.execute("insert into student(name,classid) values('小赵',1504)")
db_obj.commit() # 用数据库对象提交"""

# 执行sql：查询全部的数据
"""cursor.execute("select * from student")
result_all = cursor.fetchall()
print(result_all)"""

# 执行sql：修改，对数据库有改动的
"""cursor.execute("update student set name='小李' where id=2")
db_obj.commit()"""


# 执行sql：删除数据，对数据库有改动的
"""cursor.execute("delete from student where name='小李'")
db_obj.commit()"""

# 执行sql：查询全部的数据
"""cursor.execute("select * from student")
result_all = cursor.fetchall()
print(result_all)"""


# 每次用完数据库连接，需要手动关闭
"""cursor.close() # 关闭游标
db_obj.close() # 关闭数据库对象"""

class DB:
    def __init__(self,host,port,user,password,database):
        # ip地址 ：host
        # 端口号：port
        # 账号：user
        # 密码：password
        # 数据库的名称：database
        # cursorclass=pymysql.cursors.DictCursor ，返回的结果，以字典的方式返回
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
    def connect(self):
        self.db = pymysql.connect(host = self.host,user=self.user,password=self.password,port=self.port,database=self.database,cursorclass=pymysql.cursors.DictCursor)
        self.cursor = self.db.cursor()
    def close(self):
        self.cursor.close()
        self.db.close()
    def search_one(self,sql): # todo：返回一条数据
        result = 0
        try:
            self.connect() # 先连接
            self.cursor.execute(sql)
            result = self.cursor.fetchone()
            self.close() # 做完操作关闭，回收资源
            return result
        except:
            self.db.rollback() # 回滚
            traceback.print_exc() # 报错的信息会打印在控制台上面
            self.close()
            return result
    def search_all(self,sql): # todo：返回多条数据
        result = 0
        try:
            self.connect()  # 先连接
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            self.close()  # 做完操作关闭，回收资源
            return result
        except:
            self.db.rollback()  # 回滚
            traceback.print_exc()  # 报错的信息会打印在控制台上面
            self.close()
            return result
    def __edit(self,sql):
        result = 0
        try:
            self.connect()
            self.cursor.execute(sql)
            self.db.commit()
            self.close()
            result = 1
            return result
        except:
            self.db.rollback() # 数据库回滚一下
            self.close()
            traceback.print_exc()
            return result
    def insert(self,sql):
        self.__edit(sql)
    def update(self,sql):
        self.__edit(sql)
    def delete(self,sql):
        self.__edit(sql)

if __name__ == '__main__':
    db1 = DB(host = "47.104.65.165",port=3306,user="root",password="123456",database="jiac")
    # print(db1.search_all("select * from student"))
    # print(db1.search_one("select * from student"))
    db1.insert("insert into student(name,classid) values('小王',1507)")
    db1.update('update student set name="张三" where id = 1')
    db1.delete("delete from student where id=3")
    print(db1.search_all("select * from student"))














